﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;
using RCA1._0;

namespace Royal_1._0_DAL_WS
{
    public class ChucDanhThanhVienDAO: DataProvider
    {
        
        public DataTable LayBang()
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "select * from CHUCDANHTHANHVIEN";
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        #region "Lấy danh sách"

        public List<ChucDanhThanhVienDTO> LayDanhSach()
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "select * from CHUCDANHTHANHVIEN";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            ChucDanhThanhVienDTO tempDTO;
            List<ChucDanhThanhVienDTO> dsCHUCDANHTHANHVIEN = new List<ChucDanhThanhVienDTO>();
            while (dr.Read())
            {
                tempDTO = new ChucDanhThanhVienDTO();
                tempDTO.IDChucDanhThanhVien = Convert.ToInt32(dr["IDChucDanhThanhVien"]);
                tempDTO.TenChucDanhThanhVien = dr["TenChucDanhThanhVien"].ToString();
                dsCHUCDANHTHANHVIEN.Add(tempDTO);
            }
            return dsCHUCDANHTHANHVIEN;
        }

        #endregion


        #region "Lấy danh sách mã"

        public List<ChucDanhThanhVienDTO> LayMaChucDanhThanhVien()
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "select * from CHUCDANHTHANHVIEN";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            ChucDanhThanhVienDTO tempDTO;
            List<ChucDanhThanhVienDTO> dsCHUCDANHTHANHVIEN = new List<ChucDanhThanhVienDTO>();
            while (dr.Read())
            {
                tempDTO = new ChucDanhThanhVienDTO();
                tempDTO.IDChucDanhThanhVien = Convert.ToInt32(dr["IDChucDanhThanhVien"]);
                dsCHUCDANHTHANHVIEN.Add(tempDTO);
            }
            return dsCHUCDANHTHANHVIEN;
        }

        #endregion


        #region "Tìm kiếm"
        public ChucDanhThanhVienDTO TimTheoMa(int MaChucDanhThanhVien)
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "select * from CHUCDANHTHANHVIEN Where IDChucDanhThanhVien=" + MaChucDanhThanhVien.ToString();
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            dr.Read();
            ChucDanhThanhVienDTO tempDTO = new ChucDanhThanhVienDTO();
            tempDTO.IDChucDanhThanhVien = Convert.ToInt32(dr["IDChucDanhThanhVien"]);
            tempDTO.TenChucDanhThanhVien = dr["TenChucDanhThanhVien"].ToString();
            cn.Close(); return tempDTO;
        }

        #endregion


        #region "Thêm"

        public void Them(ChucDanhThanhVienDTO tempDTO)
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "insert into CHUCDANHTHANHVIEN(TenChucDanhThanhVien) values (@TenChucDanhThanhVien)";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@TenChucDanhThanhVien", OleDbType.LongVarWChar).Value = tempDTO.TenChucDanhThanhVien;
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        #endregion


        #region "Xóa"

        public void Xoa(int MaChucDanhThanhVien)
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "delete from CHUCDANHTHANHVIEN Where IDChucDanhThanhVien = " + MaChucDanhThanhVien.ToString();
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        #endregion


        #region "Sửa"

        public void Sua(ChucDanhThanhVienDTO tempDTO)
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "update CHUCDANHTHANHVIEN set TenChucDanhThanhVien = @TenChucDanhThanhVien where IDChucDanhThanhVien = @IDChucDanhThanhVien";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@TenChucDanhThanhVien", OleDbType.LongVarWChar).Value = tempDTO.TenChucDanhThanhVien;
            cmd.Parameters.Add("@IDChucDanhThanhVien", OleDbType.Integer).Value = tempDTO.IDChucDanhThanhVien;
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        #endregion

    }
}